﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;
using Microsoft.Data.Sqlite;
using System.Data.Common;
using System.Reflection;
using System.Diagnostics;
using MyHelper;

namespace weighing
{
    public class SqliteHelper
    {
        private static SqliteHelper? _instance;

        public static SqliteHelper Instance
        {
            get
            {
                _instance ??= new SqliteHelper();
                return _instance;
            }
        }

        private SqliteHelper()
        {

        }


        /// <summary>
        /// 
        /// </summary>     
        /// <returns></returns>
        private static string GetConnString()
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "data");
            if(!Directory.Exists(path)) Directory.CreateDirectory(path);
            string db_file = Path.Combine(path,"data.db");
            var connStr = new SqliteConnectionStringBuilder()
            {
                DataSource = db_file,
                Pooling = true,
                Mode = SqliteOpenMode.ReadWriteCreate,
                //You specified a password in the connection string,
                //but the native SQLite library 'e_sqlite3' doesn't support encryption.
                //Password = "Bnhq@1234"
            }.ConnectionString;

            return connStr;
        }


        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parameters">所需参数</param>  
        /// <returns>结果DataTable</returns>  
        public DataTable ExcuteDataTable(string sql, SqliteParameter[] parameters)
        {
            using (SqliteConnection conn = new SqliteConnection(GetConnString()))
            {
                using (SqliteCommand command = new SqliteCommand(sql, conn))
                {
                    PrintSql(sql);
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    var dt = new DataTable();
                    dt.Load(command.ExecuteReader());
                    return dt;
                }
            }
        }
        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <returns>结果DataTable</returns>  
        public DataTable ExcuteDataTable(string sql)
        {
            string connstr = GetConnString();
            using (var conn = new SqliteConnection(connstr))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                using (var command = conn.CreateCommand())
                {
                    var dt = new DataTable();
                    command.CommandText = sql;
                    command.CommandTimeout = 60;
                    PrintSql(sql);
                    var reader = command.ExecuteReader();
                
                    dt.BeginLoadData();
                    dt.Load(reader, LoadOption.PreserveChanges);
                    dt.EndLoadData();
                    return dt;
                }
            }
        }

        #region 增删改  

        /// <summary>  
        /// 增删改  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parametes">所需参数</param>  
        /// <returns>所受影响的行数</returns>  
        public int ExecuteNonQuery(string sql, SqliteParameter[]? parametes = null)
        {
            int affectedRows = 0;
            using (SqliteConnection conn = new SqliteConnection(GetConnString()))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                DbTransaction? transation = conn.BeginTransaction();
                try
                {

                    using (SqliteCommand command = conn.CreateCommand())
                    {
                        command.CommandText = sql;
                        command.CommandTimeout = 60;
                        PrintSql(sql);
                        if (parametes != null)
                        {
                            command.Parameters.AddRange(parametes);
                        }
                        affectedRows = command.ExecuteNonQuery();
                        transation.Commit();
                    }
                }

                catch (Exception e)
                {
                    transation.Rollback();
                    throw e;
                }
                finally
                {
                    //  Connection?.Close();
                }
            }
            return affectedRows;
        }
        /// <summary>
        /// 事务处理多条多条操作
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public int TransactionExecute(params string[] sqls)
        {
            int affectedRows = 0;
            using (var conn = new SqliteConnection(GetConnString()))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                DbTransaction? transation = conn.BeginTransaction();
                try
                {
                    using (SqliteCommand command = conn.CreateCommand())
                    {
                        //command.Connection = Connection;                 
                        for (int i = 0; i < sqls.Length; i++)
                        {
                            PrintSql(sqls[i]);
                            command.CommandText = sqls[i];
                            affectedRows += command.ExecuteNonQuery();
                        }
                        transation.Commit();
                    }
                }
                catch (Exception)
                {
                    affectedRows = 0;
                    transation.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return affectedRows;
        }


        /// <summary>
        /// 执行删除语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public int Delete(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 删除对像 ，支持软件删除
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <param name="isTrueDelete">是否真的删除 默认软删除</param>
        /// <returns>影响行数</returns>
        public int Delete<T>(T? obj, bool isTrueDelete = true)
        {
            if(obj == null) return 0;
            string deleteSql = SqlBuilder.GetDeleteSql(obj, isTrueDelete);
            return ExecuteNonQuery(deleteSql, null);
        }

        /// <summary>
        /// 执行修改语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public int Update(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 修改对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public int Update<T>(T obj)
        {
            string sql = SqlBuilder.GetUpdateSql(obj);
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="sql">插入语句</param>
        /// <returns>影响行数</returns>
        public int Insert(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 插入对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public int Insert<T>(T obj)
        {
            string insertSql = SqlBuilder.GetInsertSql(obj);
            return ExecuteNonQuery(insertSql, null);
        }

        /// <summary>
        /// 插入或者更新对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public int InsertOrUpdate<T>(T obj)
        {
            if (CheckExist(obj))
            {
                return Update(obj);
            }
            else
            {
                return Insert(obj);
            }
        }

        #endregion
        /// <summary>
        /// 检查对像是已经存在于数据库中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool CheckExist<T>(T obj)
        {
            Type type = typeof(T);
            try
            {
                PropertyInfo? propertyinfo = type.GetProperty("id");
                if (propertyinfo == null)
                {
                    propertyinfo = type.GetProperty("Id");
                    if (propertyinfo == null)
                    {
                        throw new Exception(SqlBuilder.buildSqlErrorMessage);
                    }
                }
                object? tempObj = propertyinfo.GetValue(obj, null);
                if (tempObj == null || tempObj.ToString().Length <= 0)
                {
                    throw new Exception(SqlBuilder.buildSqlErrorMessage);
                }
                string condition = SqlBuilder.splitChar + "id" + SqlBuilder.splitChar + "=" + SqlBuilder.valueSplitChar + tempObj.ToString() + SqlBuilder.valueSplitChar;
                string sql = SqlBuilder.GetSelectSql(SqlBuilder.GetTableName(obj), null, condition);
                DataTable dt = ExcuteDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                throw;
            }
        }


        /// <summary>
        /// 将数据解析为对像,并记录日志
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="row">Data Row</param>
        /// <param name="fArray"> 类型的属性集合，不传会根据 T 的类型获取</param>
        /// <returns></returns>
        protected T ParseEntityWithLog<T>(DataRow row, FieldInfo[] fArray = null) where T : class, new()
        {
            string msg = String.Empty;
            try
            {
                T entity = new T();
                fArray ??= entity.GetType().GetFields();
                msg = "type: " + entity.GetType().Name + "\n";
                foreach (FieldInfo filedInfo in fArray)
                {
                    msg += "\t FieldInfo : Name:" + filedInfo.Name + " FieldType:" + filedInfo.FieldType + " ";
                    string columnName = StringHelper.camelCaseToDBnameing(filedInfo.Name);
                    if (row.Table.Columns.Contains(columnName))
                    {
                        var value = row[columnName];
                        msg += "  DB column value type: " + value.GetType().Name + " Value: " + value + " \n";
                        if (value is DBNull)
                        {
                            filedInfo.SetValue(entity, null);
                            continue;
                        }
                        else if (value is DateTime dateTime)
                        {

                            if (filedInfo.FieldType.Equals(typeof(DateTime)))
                            {
                                filedInfo.SetValue(entity, value);
                            }
                            else if (filedInfo.FieldType.Equals(typeof(String)))
                            {
                                filedInfo.SetValue(entity, dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else if (filedInfo.FieldType.Equals(typeof(Int64)))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt64(value));
                            }
                            else if (filedInfo.GetType().Equals(typeof(Int32)))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt32(value));
                            }
                            else if (filedInfo.GetType().Equals(typeof(Int16)))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt16(value));
                            }
                            else
                            {
                                filedInfo.SetValue(entity, value);
                            }
                        }
                        else if (value is UInt64 u64Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, u64Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, u64Value);
                            }
                        }
                        else if (value is Int64 i64Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Int32>) || filedInfo.FieldType == typeof(Int32))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt32(i64Value));
                            }else 
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, i64Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, i64Value);
                            }
                        }
                        else if (value is Int32 i32Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, i32Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, Convert.ToInt32(i32Value));
                            }
                        }
                        else if (value is Int16 i16Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, i16Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, Convert.ToInt16(i16Value));
                            }
                        }
                        else if (value is UInt32 u32Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, u32Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, Convert.ToInt32(u32Value));
                            }
                        }
                        else if (value is UInt16 u16Value)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, u16Value > 0);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, Convert.ToInt64(u16Value));
                            }
                        }
                        else if (value is SByte sb)
                        {
                            if (filedInfo.FieldType == typeof(Nullable<Boolean>) || filedInfo.FieldType == typeof(Boolean))
                            {
                                filedInfo.SetValue(entity, sb > 0);
                            }
                            else if (filedInfo.FieldType is Int16 || filedInfo.FieldType == typeof(Nullable<Int16>))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt16(sb));
                            }
                            else if (filedInfo.FieldType is Int32 || filedInfo.FieldType == typeof(Nullable<Int32>))
                            {
                                filedInfo.SetValue(entity, Convert.ToInt32(sb));
                            }
                            else
                            {
                                filedInfo.SetValue(entity, sb);
                            }
                        }
                        else if (value is Double dv)
                        {
                            if (filedInfo.FieldType == typeof(Decimal) || filedInfo.FieldType == typeof(Nullable<Decimal>))
                            {
                                filedInfo.SetValue(entity, new Decimal(dv));
                            }
                            else if (filedInfo.FieldType == typeof(Double) || filedInfo.FieldType == typeof(Nullable<Double>))
                            {
                                filedInfo.SetValue(entity, dv);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, value);
                            }
                        }
                        else if (value is float fv)
                        {
                            if (filedInfo.FieldType == typeof(Decimal) || filedInfo.FieldType == typeof(Nullable<Decimal>))
                            {
                                filedInfo.SetValue(entity, new Decimal(fv));
                            }
                            else if (filedInfo.FieldType == typeof(Double) || filedInfo.FieldType == typeof(Nullable<Double>))
                            {
                                filedInfo.SetValue(entity, fv);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, value);
                            }
                        }
                        else if (value is string str)
                        {
                            if (filedInfo.FieldType == typeof(Decimal) || filedInfo.FieldType == typeof(Nullable<Decimal>))
                            {
                                filedInfo.SetValue(entity, Decimal.Parse(str));
                            }
                            else if (filedInfo.FieldType == typeof(Double) || filedInfo.FieldType == typeof(Nullable<Double>))
                            {
                                filedInfo.SetValue(entity, Double.Parse(str));
                            }
                            else if (filedInfo.FieldType == typeof(Int64) || filedInfo.FieldType == typeof(Nullable<Int64>))
                            {
                                filedInfo.SetValue(entity, Int64.Parse(str));
                            }
                            else if (filedInfo.FieldType == typeof(Int32) || filedInfo.FieldType == typeof(Nullable<Int32>))
                            {
                                filedInfo.SetValue(entity, Int32.Parse(str));
                            }
                            else if (filedInfo.FieldType == typeof(Boolean) || filedInfo.FieldType == typeof(Nullable<Boolean>))
                            {
                                filedInfo.SetValue(entity, str == "ture" || str == "TURE" || str == "1" || str == "True");
                            }
                            else if (filedInfo.FieldType == typeof(DateTime) || filedInfo.FieldType == typeof(Nullable<DateTime>))
                            {
                                DateTime.TryParse(str, out DateTime dt);
                                filedInfo.SetValue(entity,dt);
                            }
                            else
                            {
                                filedInfo.SetValue(entity, value);
                            }
                        }
                        else
                        {
                            filedInfo.SetValue(entity, value);
                        }
                    }
                }
                return entity;
            }
            catch (Exception ex)
            {
                if (string.IsNullOrEmpty(msg))
                {
                    Log.Error(ex.Message);
                }
                else
                {
                    string m = ex.Message + " 信息：" + msg;
                    Log.Error(m);
                }
                throw ex;
            }
        }

        private static void PrintSql(string sql)
        {
            Debug.WriteLine("======================================");
            Debug.WriteLine("SQL:" + sql);
            Debug.WriteLine("======================================");
        }


        /// <summary>
        /// 按SQl语句查询 
        /// </summary>
        /// <typeparam name="T"> 单个实体类 如 Car </typeparam>
        /// <param name="sql"></param>
        /// <returns>DataTable</returns>
        public List<T> Select<T>(string sql) where T : class, new()
        {
            DataTable dt = ExcuteDataTable(sql);
            Type type = typeof(T);
            List<T> list = new();
            FieldInfo[] pArray = type.GetFields();
            foreach (DataRow row in dt.Rows)
            {
                T t = ParseEntityWithLog<T>(row, pArray);
                list.Add(t);
            }
            dt?.Dispose();
            return list;
        }

    }
}
